-- @owner: danny20001210
-- @date: 2025-5-15
-- @testpoint:验证xmltype模块结合函数、存储过程、匿名块、触发器、内置函数使用正确，success，部分用例合理报错
@conn PrimaryDbAmode;
drop table if exists table_1268556 cascade;
drop table if exists table_1268556_1 cascade;



create table table_1268556( ID int, name clob, data xmltype default xmltype('<Config id="5">XML</Config>') );
Insert INTO table_1268556 VALUES (1,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>')) ;
Insert INTO table_1268556 VALUES (2,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^4500-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾2</subfield> <subfield code="f">奥运会2</subfield> </datafield> <datafield tag="2090" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机2</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机2</subfield> <subfield code="a">笔记本2</subfield> </datafield> </record> </collection>')) ;

--drop table if exists table_1268556 cascade;

create table table_1268556_1( ID int, name clob, data xmltype );
--create table table_1268556(id int, c1 xmltype,c2 xmltype);
--insert into table_1268556 values(1,'abc1','abc2');
--insert into table_1268556 values(2,'<test>abc1</test>','<test>abc2</test>');
--insert into table_1268556 values(3,'abc1 \153\154\155 \052\251\124','abc2 \153\154\155 \052\251\124');

select pg_typeof(extract(x.data,'/collection/record/datafield/subfield')) from table_1268556 x;
select pg_typeof(XMLSequence(extract(x.data,'/collection/record/datafield/subfield'))) from table_1268556 x;
select pg_typeof(extractvalue(x.data,'/collection/record/leader'))  from table_1268556 x;
select pg_typeof(getStringVal(extract(x.data,'/collection/record/datafield/subfield')))  from table_1268556 x;
select pg_typeof(existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]')) from table_1268556 x;

select  upper(getStringVal(extract(x.data,'/collection/record/datafield/subfield')))  from table_1268556 x;

select  upper(extractvalue(x.data,'/collection/record/leader') || 'abs')  from table_1268556 x;

select abs(existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]')) + 2 -100 /2*4 from table_1268556 x;

select extract(extract(x.data,'/collection/record/datafield[@tag="209"]'),'//subfield') from table_1268556 x;
create or replace procedure proc_test_1268556 (p1 in int, p2 text, p3 out xmltype)
as
declare result xmltype;
begin
p2:=(select id from table_1268556 where extractvalue(data,'/collection/record/leader') =p2);
p3:=(select data from table_1268556 where existsnode(data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') =p1);
end;
/


call proc_test_1268556(1,'-----nam0-22-----^^^450-','any');

select * from table_1268556 order by id;

create or replace function function_1268556 (p1 int, p2 text, p3 xmltype) return xmltype
as
PRAGMA AUTONOMOUS_TRANSACTION;
declare result xmltype;
begin
p2:=(select id from table_1268556 where extractvalue(data,'/collection/record/leader') =p2);
p3:=(select data from table_1268556 where existsnode(data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') =p1);
result:=(select data xmlseq from table_1268556 x where id =1);
return result;
end;
/

select function_1268556(1,'-----nam0-22-----^^^450-','any');

declare
begin
perform extract(x.data,'/collection/record/datafield/subfield') xmlseq from table_1268556 x;
perform XMLSequence(extract(x.data,'/collection/record/datafield/subfield')) xmlseq from table_1268556 x;
perform extractvalue(x.data,'/collection/record/leader') as A from table_1268556 x;
perform getStringVal(extract(x.data,'/collection/record/datafield/subfield')) a from table_1268556 x;
perform existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as a from table_1268556 x;
end;
/
create or replace function fun_1268556() return trigger
as
begin
insert into table_1268556_1 values(default,new.name,new.data);
return new;
end;
/

create trigger trigger_1268556 after insert on table_1268556 for each row execute procedure fun_1268556();
/

insert into table_1268556 values(11,'test xml doc new',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>new奇特-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾？？</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>'));

select * from table_1268556 order by 1;
select * from table_1268556_1 order by 1;